package org.iteam.core.service.oracle.impl.base;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.iteam.core.service.DBService;
import org.iteam.core.service.base.TreeNodeService;
import org.iteam.core.view.TreeNode;

public class TreeNodeServiceImpl extends TreeNodeService {

	public List<TreeNode> getArea() throws SQLException {
		String sql = "SELECT B.AID AS ID, B.\"NAME\" AS TEXT, B.PID AS PARENTID, ( SELECT COUNT (1) FROM BASE_AREA A WHERE A .PID = B.AID ) AS COUNT FROM BASE_AREA B WHERE B.PID IS NULL ORDER BY B.AID";
		Connection conn = DBService.getDataSource().getConnection();
		List<TreeNode> TreeNodeViewlist = null;
		try {
			List<TreeNode> list = super.query(conn, TreeNode.class, sql);
			TreeNodeViewlist = new ArrayList<TreeNode>();
			for (TreeNode area : list) {
				if (area.getCount() > 0) {
					area.setChildren(getArea(conn, area.getId()));
					if (area.getChildren() != null
							&& area.getChildren().size() > 0) {
						area.setState("closed");
					}
				}
				TreeNodeViewlist.add(area);
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (conn != null && !conn.isClosed())
				conn.close();
		}
		return TreeNodeViewlist;
	}

	private List<TreeNode> getArea(Connection conn, String pid)
			throws SQLException {
		String sql = "SELECT B.AID AS ID, B.\"NAME\" AS TEXT, B.PID AS PARENTID, ( SELECT COUNT (1) FROM BASE_AREA A WHERE A .PID = B.AID ) AS COUNT FROM BASE_AREA B WHERE B.PID = ? ORDER BY B.AID";
		List<TreeNode> TreeNodeViewlist = null;
		try {
			List<TreeNode> list = super.query(conn, TreeNode.class, sql, pid);
			TreeNodeViewlist = new ArrayList<TreeNode>();
			for (TreeNode area : list) {
				if (area.getCount() > 0) {
					area.setChildren(getArea(conn, area.getId()));
					if (area.getChildren() != null
							&& area.getChildren().size() > 0) {
						area.setState("closed");
					}
				}
				TreeNodeViewlist.add(area);
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
		}
		return TreeNodeViewlist;
	}

	public List<TreeNode> getIndustry() throws SQLException {
		String sql = "SELECT B.CODE AS ID, B.\"NAME\" AS TEXT, B.\"PARENT\" AS PARENTID, ( SELECT COUNT (1) FROM BASE_INDUSTRY A WHERE A .\"PARENT\" = B.CODE ) AS COUNT FROM BASE_INDUSTRY B WHERE B.\"PARENT\" IS NULL ORDER BY B.CODE";
		Connection conn = DBService.getDataSource().getConnection();
		List<TreeNode> TreeNodeViewlist = null;
		try {
			List<TreeNode> list = super.query(conn, TreeNode.class, sql);
			TreeNodeViewlist = new ArrayList<TreeNode>();
			for (TreeNode area : list) {
				if (area.getCount() > 0) {
					area.setChildren(getIndustry(conn, area.getId()));
					if (area.getChildren() != null
							&& area.getChildren().size() > 0) {
						area.setState("closed");
					}
				}
				TreeNodeViewlist.add(area);
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (conn != null && !conn.isClosed())
				conn.close();
		}
		return TreeNodeViewlist;
	}

	private List<TreeNode> getIndustry(Connection conn, String pid)
			throws SQLException {
		String sql = "SELECT B.CODE AS ID, B.\"NAME\" AS TEXT, B.\"PARENT\" AS PARENTID, ( SELECT COUNT (1) FROM BASE_INDUSTRY A WHERE A .\"PARENT\" = B.CODE ) AS COUNT FROM BASE_INDUSTRY B WHERE B.\"PARENT\"  = ? ORDER BY B.CODE";
		List<TreeNode> TreeNodeViewlist = null;
		try {
			List<TreeNode> list = super.query(conn, TreeNode.class, sql, pid);
			TreeNodeViewlist = new ArrayList<TreeNode>();
			for (TreeNode area : list) {
				if (area.getCount() > 0) {
					area.setChildren(getIndustry(conn, area.getId()));
					if (area.getChildren() != null
							&& area.getChildren().size() > 0) {
						area.setState("closed");
					}
				}
				TreeNodeViewlist.add(area);
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
		}
		return TreeNodeViewlist;
	}
}
